using Microsoft.Office.Interop.Excel;
using System;
using System.Data;
using System.IO;
using System.Reflection;
using System.Text.RegularExpressions;
using DataTable = System.Data.DataTable;

namespace OrderSystem.FormatOutput
{
    public class ExcelToOrder
	{
		private _Application app;

		private _Workbook workBook;

		private _Worksheet workSheet;

		private Range range;

		private object missing = Missing.Value;

		private void OpenExcel(string templetFile)
		{
			if (templetFile == null)
			{
				throw new ArgumentException("你指定的参数：templetFile 为空", "templetFile");
			}
			if (!File.Exists(templetFile))
			{
				throw new ArgumentException("你指定的参数：templetFile Excel模板文件不存在", "templetFile");
			}
			this.app = new ApplicationClass();
			this.app.Visible=true;
			this.workBook = this.app.Workbooks.Open(templetFile, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing, this.missing);
			this.workSheet = (_Worksheet)this.workBook.Worksheets[1];
		}

		private bool ValidateExcel()
		{
			int num = 2;
			while (true)
			{
				bool flag = false;
				for (int i = 1; i <= 8; i++)
				{
					this.range = (Range)this.workSheet.Cells[num, i];
					if (this.range.Text != null && this.range.Text.ToString().Trim() != "")
					{
						flag = true;
						break;
					}
				}
				if (!flag)
				{
					break;
				}
				this.range = (Range)this.workSheet.Cells[num, 3];
				if (this.range.Text == null || this.range.Text.ToString().Trim() == "" || !Regex.IsMatch(this.range.Text.ToString().Trim(), "^(([0-9]{1,8})|([0-9]{1,8}\\.[0-9]{1,4}))$"))
				{
					goto Block_6;
				}
				this.range = (Range)this.workSheet.Cells[num, 4];
				if (this.range.Text == null || this.range.Text.ToString().Trim() == "" || !Regex.IsMatch(this.range.Text.ToString().Trim(), "^(([0-9]{1,8})|([0-9]{1,8}\\.[0-9]{1,4}))$"))
				{
					goto Block_9;
				}
				this.range = (Range)this.workSheet.Cells[num, 5];
				if (this.range.Text == null || this.range.Text.ToString().Trim() == "" || !Regex.IsMatch(this.range.Text.ToString().Trim(), "^[0-9]{1,8}$"))
				{
					goto Block_12;
				}
				this.range = (Range)this.workSheet.Cells[num, 7];
				if (this.range.Text == null || this.range.Text.ToString().Trim() == "" || !Regex.IsMatch(this.range.Text.ToString().Trim(), "^(([0-9]{1,})|([0-9]{1,}\\.[0-9]{1,4}))$"))
				{
					goto Block_15;
				}
				num++;
			}
			return true;
			Block_6:
			throw new Exception("行:" + num.ToString() + ";列:3，长度应该为大于零的正整数，如有小数，最大为4位小数");
			Block_9:
			throw new Exception("行:" + num.ToString() + ";列:4，宽度应该为大于零的正整数，如有小数，最大为4位小数");
			Block_12:
			throw new Exception("行:" + num.ToString() + ";列:5，数量应该为大于零的正整数");
			Block_15:
			throw new Exception("行:" + num.ToString() + ";列:7，价格应该为不为负的数字，如有小数，最大为4位小数");
		}

		public DataTable GetOrderDataFromExcel(string sFilePath)
		{
			this.OpenExcel(sFilePath);
			DataTable result;
			if (!this.ValidateExcel())
			{
				result = null;
			}
			else
			{
				DataTable dataTable = new DataTable("Product");
				dataTable.Columns.Add(new DataColumn("ProductType", typeof(string)));
				dataTable.Columns.Add(new DataColumn("ProductCode", typeof(string)));
				dataTable.Columns.Add(new DataColumn("Width", typeof(decimal)));
				dataTable.Columns.Add(new DataColumn("High", typeof(decimal)));
				dataTable.Columns.Add(new DataColumn("Quantity", typeof(int)));
				dataTable.Columns.Add(new DataColumn("Area", typeof(decimal)));
				dataTable.Columns.Add(new DataColumn("Price", typeof(decimal)));
				dataTable.Columns.Add(new DataColumn("Remark", typeof(string)));
				int num = 2;
				while (true)
				{
					bool flag = false;
					for (int i = 1; i <= 8; i++)
					{
						this.range = (Range)this.workSheet.Cells[num, i];
						if (this.range.Text != null && this.range.Text.ToString().Trim() != "")
						{
							flag = true;
							break;
						}
					}
					if (!flag)
					{
						break;
					}
					DataRow dataRow = dataTable.NewRow();
					this.range = (Range)this.workSheet.Cells[num, 1];
					if (this.range.Text != null && this.range.Text.ToString().Trim() != "")
					{
						dataRow[0] = this.range.Text;
					}
					else
					{
						dataRow[0] = "";
					}
					this.range = (Range)this.workSheet.Cells[num, 2];
					if (this.range.Text != null && this.range.Text.ToString().Trim() != "")
					{
						dataRow[1] = this.range.Text;
					}
					else
					{
						dataRow[1] = "";
					}
					this.range = (Range)this.workSheet.Cells[num, 3];
					dataRow[2] = Convert.ToDecimal(this.range.Text);
					this.range = (Range)this.workSheet.Cells[num, 4];
					dataRow[3] = Convert.ToDecimal(this.range.Text);
					this.range = (Range)this.workSheet.Cells[num, 5];
					dataRow[4] = Convert.ToInt32(this.range.Text);
					dataRow[5] = Convert.ToDecimal((Convert.ToDecimal(dataRow[2]) * Convert.ToDecimal(dataRow[3]) / 1000000m).ToString("F4")) * Convert.ToInt32(dataRow[4]);
					this.range = (Range)this.workSheet.Cells[num, 7];
					dataRow[6] = Convert.ToDecimal(this.range.Text);
					this.range = (Range)this.workSheet.Cells[num, 8];
					if (this.range.Text != null && this.range.Text.ToString().Trim() != "")
					{
						dataRow[7] = this.range.Text;
					}
					else
					{
						dataRow[7] = "";
					}
					dataTable.Rows.Add(dataRow);
					num++;
				}
				this.app.Quit();
				dataTable.AcceptChanges();
				result = dataTable;
			}
			return result;
		}
	}
}
